package com.clps.dev.sms.util.excel;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**  
* @ClassName: ExcelTool  
* @Description: excel工具类
* @since JDK 1.8 
* @author RuiShan.Yang  
* @date 2018-11-28 13:44:36  
* 
*/ 
public class ExcelTool {

	/**  
	* @Title: importexcel  
	* @Description:(将excel表格封装成对象)  excel第一行为类名,第二行属性名
	* @param @param excelUrl
	* @param @return
	* @param @throws Exception    参数   excel地址
	* @return ArrayList<T>    返回类型  
	* @throws  
	*/

	public static <T> void exportExcel(List<T> list, String path) {
		try {
			FileOutputStream outputStream = new FileOutputStream(path);
			ExcelTool.exportWorkbook(list).write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * @Title: exportExcel @Description:  
	 *         list @param @return @param @throws Exception 参数 @return Workbook
	 *         返回类型 @throws
	 */
	public static <T> Workbook exportWorkbook(List<T> list) throws Exception {

		if (list.isEmpty()) {

			return null;
		}

		XSSFWorkbook workbook = new XSSFWorkbook();

		String[] title = getTitle(list.get(0));
		CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, title.length - 1);// 合并单元格
		Sheet sheet = workbook.createSheet();
		sheet.addMergedRegion(cellRangeAddress);
		sheet.setDefaultColumnWidth(20);
		sheet.setDefaultRowHeightInPoints(20);

		// 标题行
		XSSFCellStyle style0 = createCellStyle(workbook, (short) 16); // 头标题
		Row row0 = sheet.createRow(0);
		Cell cell0 = row0.createCell(0);
		cell0.setCellStyle(style0);
		String str = list.get(0).getClass().getName();
		String[] strings = str.split("\\.");
		cell0.setCellValue(strings[strings.length - 1]);

		// 属性行
		XSSFCellStyle style1 = createCellStyle(workbook, (short) 13);// 列标题
		Row row1 = sheet.createRow(1);
		for (int i = 0; i < title.length; i++) {
			Cell cell1 = row1.createCell(i);
			cell1.setCellStyle(style1);
			cell1.setCellValue(title[i]);
		}

		// 操作单元格
		for (int i = 0; i < list.size(); i++) {
			Row row2 = sheet.createRow(i + 2);
			XSSFCellStyle style2 = workbook.createCellStyle();
			style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
			String[] string = getValue(list.get(i));

			for (int k = 0; k < string.length; k++) {
				Cell cell2 = row2.createCell(k);
				cell2.setCellStyle(style2);
				cell2.setCellValue(string[k]);
			}
		}

		return workbook;

	}

	@SuppressWarnings("unchecked")
	public static <T> ArrayList<T> importexcel(String excelPath) throws Exception {
		ArrayList<T> arrayList = new ArrayList<T>();
		FileInputStream inputStream = new FileInputStream(excelPath);
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rowIterator = sheet.rowIterator();
		// 得到类名
		Row row0 = rowIterator.next();
		String classname = row0.getCell(0).toString();
		// 得到列宽
		Row row1 = rowIterator.next();
		short s = row1.getLastCellNum(); // s = 3
		// 得到方法名
		String[] mat = new String[s];
		for (int i = 0; i < s; i++) {
			mat[i] = "set" + row1.getCell(i).toString();
		}

		String clazzPath = getpath(classname); // 反射路径
		System.out.println(classname);
		System.out.println(clazzPath);
		Class<?> clazz = Class.forName(clazzPath);
		System.out.println(clazz);
		Object object = clazz.newInstance();
		Method[] methods = clazz.getMethods();

		while (rowIterator.hasNext()) {
			Row row2 = rowIterator.next(); // 拿到数据 一个对象

			for (int j = 0; j < methods.length; j++) {

				String methodName = methods[j].getName();

				if (methodName.startsWith("set")) {

					int i = 0;
					for (; i < mat.length; i++) {
						if (mat[i].equals(methodName)) {
							break;
						}

					}

					Type type = methods[j].getGenericParameterTypes()[0];

					String string2 = row2.getCell(i).toString();
					methods[j].invoke(object, TypeFormat.typeFar(type.getClass(), string2));

				}
			}
			arrayList.add((T) object);
		}
		return arrayList;

	}

	/**  
	* @Title: getpath  
	* @Description:(输入class名,返回地址)  
	* @param @param clazzString
	* @param @return    参数  
	* @return String    返回类型  
	* @throws  
	*/
	private static String getpath(String clazzName) {

		File file = new File("src");
		String string = getFile(file, clazzName);
		return string;
	}

	/**  
	* @Title: getFile  
	* @Description: (输入class名,返回地址)  
	* @param @param file
	* @param @param clazzString
	* @param @return    参数  
	* @return String    返回类型  
	* @throws  
	*/
	private static String getFile(File file, String clazzString) {
		String s = null;

		File[] dFiles = file.listFiles();

		for (int i = 0; i < dFiles.length; i++) {

			if (dFiles[i].isFile()) {
				String clazzname = (dFiles[i].getName().split("\\.")[0]);

				if (clazzname.equals(clazzString)) {
					String string = dFiles[i].getPath().substring(4);
					String string1 = string.split("\\.")[0];
					s = string1.replace("\\", ".");
				}
			} else {
				File file2 = dFiles[i];
				s = getFile(file2, clazzString);
			}
			if (s!=null) {
				break;
			}
			
		}
		System.out.println(s);
		return s;
	}

	// -----------------------------------------------------------------
	/**
	 * @Title: geetTitle @Description: (获得对象内所有的get方法名) @param @param
	 *         t @param @return 参数 @return String[] 返回类型 @throws
	 */
	private static <T> String[] getTitle(T t) {

		Class<? extends Object> clazz = t.getClass();
		Method[] methods = clazz.getMethods();
		String[] strings = new String[methods.length];
		int i = 0;
		for (int j = 0; j < methods.length; j++) {
			String methodName = methods[j].getName();
			if (methodName.equals("getClass")) {
				continue;
			}
			if (methodName.startsWith("get")) {

				// strings[i] = methodName.substring(3) + "[" +
				// methods[j].getGenericReturnType() + "]";
				strings[i] = methodName.substring(3);
				i++;
			}
		}
		String[] strings2 = null;
		out: for (int j = 0; j < strings.length; j++) {
			while (strings[j] == null) {
				strings2 = new String[j];

				break out;
			}
		}

		for (int j = 0; j < strings2.length; j++) {
			strings2[j] = strings[j];
		}
		return strings2;
	}

	/**
	 * @Title: getValue @Description:(传入对象,返回属性值) @param @param
	 *         t @param @return @param @throws Exception 参数 @return String[]
	 *         返回类型 @throws
	 */
	private static <T> String[] getValue(T t) throws Exception {

		String[] strings = getTitle(t);

		for (int j = 0; j < strings.length; j++) {

			// String getString = "get" + strings[j].split("\\[")[0];
			String getString = "get" + strings[j];
			System.out.println(getString);
			Method method = t.getClass().getMethod(getString);
			Object object = method.invoke(t);

			String string = object + "";
			strings[j] = string;
		}
		return strings;

	}

	/**
	 * @Title: createCellStyle @Description: (居中,样式设计) @param @param
	 *         workbook @param @param s @param @return 参数 @return XSSFCellStyle
	 *         返回类型 @throws
	 */
	private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short s) {
		XSSFCellStyle style = workbook.createCellStyle();
		// 居中
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 字体
		Font font = workbook.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints(s);
		style.setFont(font);
		return style;
	}

}
